﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace LinkSql
{
    public class MySQLUtils
    {
        //MySQL数据库连接字符串
        private static string connectionString = @"server=localhost;User Id=root;password=123456;database=registration_system;CharacterSet=utf8";

        public static MySqlConnection GetMySQLConnection()
        {
            MySqlConnection sqlcon = new MySqlConnection(connectionString);//创建MySQL连接
            return sqlcon;
        }




        public static bool insert(string tableName, Dictionary<string, Object> map)
        {
            MySqlConnection connection = GetMySQLConnection();
            try
            {
                string sql = "insert into " + tableName + "(";
                int num = map.Count();
                foreach (string key in map.Keys)
                {
                    num--;
                    sql += "" + key;
                    if (num != 0)
                        sql += ",";
                    else
                        sql += ") ";
                }
                sql += "values(";
                num = map.Count();
                foreach (string key in map.Keys)
                {
                    num--;
                    sql += "'" + map[key] + "'";
                    if (num != 0)
                        sql += ",";
                    else
                        sql += ") ";
                }
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();


            }
            catch (MySqlException)
            {

                return false;
            }
            finally
            {
                connection.Close();
            }

            return true;
        }

        public static bool delete(string tableName,int id)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "delete from "+ tableName + " where id = " + id;
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }

        public static bool update(string tableName, Dictionary<string, Object> map)
        {

            MySqlConnection connection = GetMySQLConnection();
            string sql = "update " + tableName + " set ";
            int num = map.Count();
            foreach (string key in map.Keys)
            {
                num--;
                sql += "" + key + "='" + map[key] + "'";
                if (num != 0)
                    sql += ",";
                else
                    sql += " where id =" + map["id"];
            }
            Console.WriteLine(sql);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;

        }


        public static DataTable selectById(string tableName,int id)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            DataTable dataTable = new DataTable();
            try
            {
                connection.Open();
                string sql = "select * from "+ tableName + " where id=" + id;
                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
                
                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return dataTable;
                }
            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {
                connection.Close();
            }
            return dataTable;
        }

        public static DataTable selectAll(string tableName)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            DataTable dataTable = new DataTable();
            try
            {
                connection.Open();
                string sql = "select * from " + tableName ;
                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, connection);

                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return dataTable;
                }
            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {
                connection.Close();
            }
            return dataTable;
        }

        public static DataTable selectByMap(string tableName, Dictionary<string,object> map)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            DataTable dataTable = new DataTable();
            try
            {
                connection.Open();
                string sql = "select * from " + tableName + " where ";
                int num = map.Count;
                foreach (string key in map.Keys)
                {
                    num--;
                    sql += "" + key + "= '"+map[key]+"'";
                    if (num != 0)
                        sql += " and ";
                }
                Console.WriteLine(sql);
                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, connection);

                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return dataTable;
                }

            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {
                connection.Close();
            }
            return dataTable;
        }

    }
}
